SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[getInterest]
 (
	@date         DATETIME,
      @lot          INT,
      @PortfolioID  VARCHAR(max),
      @instrumentID INT
 )
  RETURNS @tbl TABLE
                    (
            --                        tradeid INT,
									   --instrName   varchar(MAX)
            --                         --, par         DECIMAL(38, 10)
                                     --, amortCost   DECIMAL(38, 10)
                                     --, mktVal      DECIMAL(38, 10)
                                      settleDis   DECIMAL(38, 10)
                                     , coupon      DECIMAL(38, 10)
                                     , pik         DECIMAL(38, 10)
                                     , totalAnnint DECIMAL(38, 10)
                                     , lotPar DECIMAL(38,10)
									
  , passedDate DATetime          , ExpID INT       , exportNAVDATE DATETIME
  , tradeDate DATETIME,
  tradetype VARCHAR(MAX	)
  , instrname VARCHAR(MAX	)
                    )
 AS
      BEGIN
           INSERT INTO @tbl
           
           
           SELECT --  NULL,   
--NULL				--	i.InstrName
		   
                    --, SUM(a.EndAdjAmt
                    --, SUM(a.AmortCost
                    --, SUM(a.MktVal
                     a.SettDiscPurchPrc
                    , a.CoupRevAnnualPurch
                    , a.PIKPurchAmt
                    , a.SettDiscPurchPrc + a.CoupRevAnnualPurch + a.PIKPurchAmt
                    , a.EndAdjAmt
					, e.NAVDate
					, a.ExportId
					, t.TradeDate
					, @date
					, t.TradeType
					, i.InstrName
										
 FROM FSFundModel_VALCOMM.Reporting.Trade t

INNER JOIN  FSFundModel_VALCOMM.Reporting.Asset a ON
t.ExportId = a.ExportId 
AND t.LotId = a.LotId
AND t.InstrumentId = a.InstrumentId
AND t.PortfolioId = a.PortfolioId

INNER JOIN  FSFundModel_VALCOMM.Reporting.Instrument i
ON t.ExportId = i.ExportId
AND a.InstrumentId = i.InstrumentId


INNER JOIN  FSFundModel_VALCOMM.Reporting.Export e 
ON t.ExportId = e.ExportId
WHERe

            a.LotId = @lot
		   AND a.InstrumentId = @instrumentID
		   AND a.PortfolioId = @PortfolioID
 AND e.FundId = 'FSIC'
AND t.ExportId = (SELECT MAX(e.ExportId) FROM FSFundModel_VALCOMM.Reporting.Export e WHERE e.FundId ='FSIC' AND NAVDate = @date)
--AND         t.TradeDate  > '3/29/2013'
 --AND         t.TradeDate  < '7/1/2013'
 AND         a.PropReason = 'Proprietary'
 
             --GROUP BY i.InstrumentId, i.InstrName, a.SettDiscPurchPrc, a.CoupRevAnnualPurch, a.PIKPurchAmt
 AND i.InstrName LIKE '%carte%'
 AND a.AssetId= 'fseb-354'
 
 
 
 
 	;
	RETURN
 END

GO
